package edu.ec.tesis.prototiporecomendador.ngc.UserProfile;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

public class ConnectionDB {
		
	public static boolean createDatabase(String url, String dbname, String dbuser,
			String dbpass) {
		boolean	created = false;
		Connection con = null;
		int ret = 0;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url, dbuser,dbpass);
			ret = con.createStatement().executeUpdate("CREATE DATABASE "+dbname+";");
			if(ret>0){
				created = true;
				System.out.println("Se ha creado la base de datos '"+dbname+"'");
			}
			con.close();
		}catch(Exception e){
			//posiblemente la base ya exista, es un error controlado
		}
		return created;
	}

	public static boolean existsDatabase(String url,String dbname, String dbuser,
			String dbpass) {
		Connection con = null;
		boolean exist = false;
		try{
			//EL driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url, dbuser,dbpass);
			if(con.isClosed()){
				exist = false;
			}else{
				ResultSet res = con.createStatement().executeQuery("SHOW DATABASES;");
				while(res.next()){
					if(res.getString(1).equals(dbname)){
						exist = true;
						break; //ends while loop
					}
				}			
				res.close();
			}
                        con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en la conexion a la bd " + e.getMessage());
		}
                
		return exist;
	}
	
        
        public static boolean existsTable(String url,String dbname, String dbuser,String dbpass,String table) {
		Connection con = null;
		boolean exist = false;
		try{
			//EL driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url+dbname, dbuser,dbpass);
			
			ResultSet res = con.createStatement().executeQuery("show tables");
                               while(res.next()){
					if(res.getString(1).equals(validarGenero(table.toLowerCase()))){
						exist = true;
						break; //ends while loop
					}
				}			
				res.close();
			
                        con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en la conexion a la bd " + e.getMessage());
		}
                
		return exist;
	}
        
	public static void createTable(String url, String dbname, String dbuser,
			String dbpass, String table) {

		Connection con = null;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url+dbname, dbuser,dbpass);
			con.createStatement().executeUpdate("CREATE TABLE "+validarGenero(table)+";");			
			con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion al crear la tabla, posiblemente ya exista "+validarGenero(table)+": "+e.getMessage());
		}
                finally{
                }
		
	}
	
	public static void dropTable(String url, String dbname, String dbuser,
			String dbpass, String table) {

		Connection con = null;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url+dbname, dbuser,dbpass);
			con.createStatement().executeUpdate("DROP TABLE "+validarGenero(table)+";");			
			con.close();
		}catch(Exception e){
			//posiblemente la tabla no exista, es un error controlado
		}
		
	}
	
	public static void insert(String url, String dbname, String dbuser,
			String dbpass, String table, String user,String values){
		Connection con = null;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url+dbname, dbuser,dbpass);
                        ResultSet res = con.createStatement().executeQuery("select * from "+validarGenero(table)+" where user='"+user+"'");
                        if(!res.next())
			con.createStatement().executeUpdate("INSERT INTO "+validarGenero(table)+" VALUES "+values+";");	
                        else
                        {
                            con.createStatement().execute("delete from "+validarGenero(table)+" where user='"+user+"';");
                            con.createStatement().executeUpdate("INSERT INTO "+validarGenero(table)+" VALUES "+values+";");	
                        }
                            con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en insert query(tabla:"+validarGenero(table)+"): "+e.getMessage());
		}
	}
	
	public static float select(String url, String dbname, String dbuser,
			String dbpass, String table, String value){
		Connection con = null;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url+dbname, dbuser,dbpass);
			ResultSet rs = con.createStatement().executeQuery("SELECT doi FROM "+validarGenero(table)+" WHERE user='"+value+"';");			
			float doi = -1;
			while(rs.next()){
				doi = rs.getFloat(1);
			}
			rs.close();
			con.close();
			return doi;			
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en insert query: "+e.getMessage());
		}
		return -1;
	}
        
        
 public static String[][] selectFavorites(String url, String dbname, String dbuser,String dbpass,int idUser)
 {
		Connection con = null;
                String[][] resultado=new String[2][3];

                
                try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(url+dbname, dbuser,dbpass);
			ResultSet res = con.createStatement().executeQuery("show tables");			
			float doi = -1;
			while(res.next()){
                        if(!res.getString(1).equals("user") || !res.getString(1).equals("neighbors")){
                            ResultSet rs = con.createStatement().executeQuery("SELECT doi FROM "+res.getString(1)+" WHERE user='"+idUser+"' and num>3;");	
                            
                        while(rs.next())
                            {    
                            for(int i=0;i<3;i++)
                            {   
                                float aux=-1;
                                if(resultado[0][i]==null)
                                {
                                    resultado[0][i]=res.getString(1);
                                    aux=rs.getFloat(1);
                                    resultado[1][i]=Float.toString(aux);
                                    break;
                                }else
                                {
                                    
                                    if(Float.parseFloat(resultado[1][i])<rs.getFloat(1))
                                    {
                                        if(i==0)
                                        {   
                                            resultado[0][2]=resultado[0][1];
                                            resultado[1][2]=resultado[1][1];
                                            resultado[0][1]=resultado[0][0];
                                            resultado[1][1]=resultado[1][0];
                                            resultado[0][0]=res.getString(1);
                                            aux=rs.getFloat(1);
                                            resultado[1][0]=Float.toString(aux);
                                            break;
                                            
                                        }
                                        else if(i==1)
                                        {
                                            resultado[0][i+1]=resultado[0][i];
                                            resultado[1][i+1]=resultado[1][i];
                                            resultado[0][i]=res.getString(1); aux=rs.getFloat(1);
                                            resultado[1][i]=Float.toString(aux);
                                            break;
                                            
                                         }
                                        else
                                        {
                                             resultado[0][i]=res.getString(1); aux=rs.getFloat(1);
                                            resultado[1][i]=Float.toString(aux);
                                            break;
                                        }
                                    }
                                }
                            }
                            }
                        rs.close();
                           }
                        }
			res.close();
			con.close();
						
		}catch(Exception e){
//			System.out.println("Se a producido una excepcion: "+e.getMessage());
		}
		return resultado;
	
 }
	
 private static String validarGenero(String genero) {

        if(genero.contains("-"))
        {
            String aux=genero.replace("-", "");
            return aux;
        }
        if(genero.contains("."))
        {
            String aux=genero.replace(".", "");
            return aux;
        }
        return genero;
    }

    public static ResultSet getNearest(int idUser,String genero1, String doi1, String genero2, String doi2, String genero3, String doi3, int numNeigborgs, int numMin,float disMax,Configuration conf) {
            Connection con = null;
            ResultSet rs=null;
             try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(conf.getURL()+conf.getDBNAME(), conf.getDBUSER(),conf.getDBPASS());
                        String consulta="select * from ((select c1.u1 as user, sqrt(difdoi1+difdoi2+difdoi3) as suma "+
                                    "from ((SELECT user as u1,pow(doi-"+doi1+",2) as difDoi1 FROM "+genero1+
                                    " where  num>"+numMin+" order by difdoi1) as c1),"+
                                    "((SELECT user as u2,pow(doi-"+doi2+",2) as difDoi2 FROM  "+genero2+
                                    " where   num>"+numMin+" order by difdoi2)as c2),"+
                                    "((SELECT user as u3,pow(doi-"+doi3+",2) as difDoi3 FROM "+genero3+
                                    " where num>"+numMin+" order by difDoi3)as c3) "+
                                    " where c1.u1=c2.u2 and c2.u2=c3.u3 and c1.u1!="+idUser+"  order by suma limit "+numNeigborgs+
                                    " ) as ct ) where ct.suma<"+disMax
                                
                                    ;
                        rs = con.createStatement().executeQuery(consulta);	
                            
                        
             }catch(Exception e){
			System.out.println("Se a producido una excepcion: "+e.getMessage());
		}
                return rs;
    }

    static void insertNeighborg(Configuration conf, int idUser, String nei, Float fl) {
    			
		Connection con = null;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
                        con = DriverManager.getConnection(conf.getURL()+conf.getDBNAME(), conf.getDBUSER(),conf.getDBPASS());
                       con.createStatement().executeUpdate("INSERT INTO neighbors VALUES ("+idUser+","+nei+","+fl+");");	
                       con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en insert en neightborg: "+e.getMessage());
		}
    
    }
    
    static void deleteNeighborgs(Configuration conf, int idUser)
    {
        Connection con = null;
		
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(conf.getURL()+conf.getDBNAME(), conf.getDBUSER(),conf.getDBPASS());
                        con.createStatement().execute("delete from neighbors where user='"+idUser+"';");
                                   con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en insert en neightborg: "+e.getMessage());
		}
                        
                        
    }

    static ArrayList<String> selectNeighbors(Configuration conf, int idUser) {
    		Connection con = null;
		ArrayList<String> ite = new ArrayList<String>();
		try{
			//El driver JDBC ya se ha cargado anteriormente
                        con = DriverManager.getConnection(conf.getURL()+conf.getDBNAME(), conf.getDBUSER(),conf.getDBPASS());
                       ResultSet rs=con.createStatement().executeQuery("select neighbor from neighbors where user="+idUser);
                       while(rs.next())
                       {
                          String fl=rs.getString("neighbor");
                           ite.add(fl);
                           
                       }
                       con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en select en neightborg: "+e.getMessage());
		}
                return ite;
    
    
    }

    static double countNeighborgs(Configuration conf) {
          Connection con = null;
		double num=0;
		try{
			//El driver JDBC ya se ha cargado anteriormente
			con = DriverManager.getConnection(conf.getURL()+conf.getDBNAME(), conf.getDBUSER(),conf.getDBPASS());
                        ResultSet rs=con.createStatement().executeQuery("select count(*) as num from neighbors ;");
                        rs.next();
                               num=rs.getDouble("num");
                        con.close();
		}catch(Exception e){
			System.out.println("Se a producido una excepcion en insert en neightborg: "+e.getMessage());
		}
                return num;
    }
	
}
